﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
/// JIFEN ADDED FOR GENGERRATE ACCOUNTNO.
/// </summary>
public class AccountsManager
{

    public String GenerateNo(string type)
    {
        // type must belong to "SAVING,FIXDEPOSIT,CARDS,INSURANCE,CUSTOMER"
        if (type != "FILE" && type != "POLICY")
        {
            return null;
        }


        int currentMaxNo = 0;
        String prefix = "";
        int seed = 1;
        int incr = 1;
        var conn = DBManager.getSqlConnection();
        conn.Open();
        string sql = "select currval,prefix,seed,incr from AllSequences where seqName=@seqName";
        var cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add(new SqlParameter("@seqName", type));
        SqlDataReader reader = cmd.ExecuteReader();
        if (reader.Read())
        {
            currentMaxNo = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); //length of int
            prefix = reader.IsDBNull(1) ? "" : reader.GetString(1);
            seed = reader.GetInt32(2);
            incr = reader.GetInt32(3);

        }
        reader.Close();
        if (currentMaxNo == 0)
        {
            currentMaxNo = seed;
        }
        else
        {
            currentMaxNo = currentMaxNo + incr;
        }

        sql = "UPDATE  AllSequences SET currval=@currval where seqName=@seqName"; //seqName = type
        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add(new SqlParameter("@seqName", type));
        cmd.Parameters.Add(new SqlParameter("@currval", currentMaxNo));
        int row = cmd.ExecuteNonQuery();
        conn.Close();


        return prefix + currentMaxNo;

    }


    public Dictionary<string,Object> getCustomerInfo(string userId)
    {
        Dictionary<string, Object> customerInfo = new Dictionary<string, Object>();

        customerInfo.Add("accountID",123144);


        return customerInfo;
    }


    public String GenerateRandomPass()
    {
        var chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        var random = new Random();
        var result = new string(
            Enumerable.Repeat(chars, 8)
                      .Select(s => s[random.Next(s.Length)])
                      .ToArray());


        return result;
    }

    public int getMaxValue(string tableName, string columnName)
    {
        string sql = "select Max(" + columnName + ") from " + tableName;
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
        SqlCommand cmd = new SqlCommand(sql, con);
        SqlDataReader dr = cmd.ExecuteReader();
        int maxValue = 0;
        if (dr.Read())
        {
            maxValue = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
        }
        dr.Close();

        return maxValue;
    }



    public String getInsuranceAccountNo(String custID, String category)
    {
        string sql = "select accountNo from Insurance I where I.productType =@productType and I.category =@category";
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
        SqlCommand cmd = new SqlCommand(sql, con);

        cmd.Parameters.AddWithValue("@productType", "INSURANCE");
        cmd.Parameters.AddWithValue("@category", category);
        SqlDataReader dr = cmd.ExecuteReader();
        String insurance_acc = "";
        if (dr.Read())
        {
            insurance_acc = dr.GetString(0);
        }
        dr.Close();
        con.Close();

        return insurance_acc;
    }

    public Hashtable getInsuranceInfo(int insuredID, SqlConnection con, SqlTransaction myTrans)
    {
        string sql = "SELECT I.custID, I.premiumPeriod, I.category, D.insuredID, D.accountNo, D.fName, D.lName, D.NRIC, D.nationality, D.gender, D.marital, D.birth, D.planType, D.relationship, " +
                       "  D.occupation, D.telephone, D.longStay, D.mobile, D.email, D.address, D.postalCode, D.employerName, D.employerAddress, D.business, D.illness, D.illDuration,  " +
                       "  D.illTreatment, D.illhospital, D.DeclinedHistory, D.foreseeable, D.bloodPressureIllSystolic, D.bloodPressureIllDate, D.bloodPressureIllHospital,  " +
                       "  D.HyperlipidaemiaLevel, D.HyperlipidaemiaDate, D.HyperlipidaemiaHospital, D.PremiumLevel, D.Premium, D.paidCount, D.terminateReason, D.terminateDate, " +
                       "  D.policyStatus, D.startDate, D.endDate, D.docPath " +
                       "  FROM INSURED AS D INNER JOIN " +
                       "  INSURANCE AS I ON D.accountNo = I.accountNo  " +
                        "  WHERE  D.insuredID = @insuredID";

        SqlCommand cmd = new SqlCommand(sql, con, myTrans);

        cmd.Parameters.AddWithValue("@insuredID", insuredID);
        SqlDataReader dr = cmd.ExecuteReader();
        var insuredInfo = new Hashtable();
        if (dr.Read())
        {
            insuredInfo.Add("custID", dr.IsDBNull(0) ? "" : dr.GetString(0));
            insuredInfo.Add("premiumPeriod", dr.IsDBNull(1) ? "" : dr.GetString(1));
            insuredInfo.Add("category", dr.IsDBNull(2) ? "" : dr.GetString(2));
            insuredInfo.Add("insuredID", dr.GetInt32(3));
            insuredInfo.Add("accountNo", dr.IsDBNull(4) ? "" : dr.GetString(4));
            insuredInfo.Add("fName", dr.IsDBNull(5) ? "" : dr.GetString(5));
            insuredInfo.Add("lName", dr.IsDBNull(6) ? "" : dr.GetString(6));
            insuredInfo.Add("NRIC", dr.IsDBNull(7) ? "" : dr.GetString(7));
            insuredInfo.Add("nationality", dr.IsDBNull(8) ? "" : dr.GetString(8));
            insuredInfo.Add("gender", dr.IsDBNull(9) ? "" : dr.GetString(9));
            insuredInfo.Add("marital", dr.IsDBNull(10) ? "" : dr.GetString(10));
            insuredInfo.Add("birth", dr.IsDBNull(11) ? DateTime.MinValue : dr.GetDateTime(11));
            insuredInfo.Add("planType", dr.IsDBNull(12) ? "" : dr.GetString(12));
            insuredInfo.Add("relationship", dr.IsDBNull(13) ? "" : dr.GetString(13));
            insuredInfo.Add("occupation", dr.IsDBNull(14) ? "" : dr.GetString(14));
            insuredInfo.Add("telephone", dr.IsDBNull(15) ? "" : dr.GetString(15));
            insuredInfo.Add("longStay", dr.IsDBNull(16) ? "" : dr.GetString(16));
            insuredInfo.Add("mobile", dr.IsDBNull(17) ? "" : dr.GetString(17));
            insuredInfo.Add("email", dr.IsDBNull(18) ? "" : dr.GetString(18));
            insuredInfo.Add("address", dr.IsDBNull(19) ? "" : dr.GetString(19));
            insuredInfo.Add("postalCode", dr.IsDBNull(20) ? "" : dr.GetString(20));
            insuredInfo.Add("employerName", dr.IsDBNull(21) ? "" : dr.GetString(21));
            insuredInfo.Add("employerAddress", dr.IsDBNull(22) ? "" : dr.GetString(22));
            insuredInfo.Add("business", dr.IsDBNull(23) ? "" : dr.GetString(23));
            insuredInfo.Add("illness", dr.IsDBNull(24) ? "" : dr.GetString(24));
            insuredInfo.Add("illDuration", dr.IsDBNull(25) ? "" : dr.GetString(25));
            insuredInfo.Add("illTreatment", dr.IsDBNull(26) ? "" : dr.GetString(26));
            insuredInfo.Add("illhospital", dr.IsDBNull(27) ? "" : dr.GetString(27));
            insuredInfo.Add("DeclinedHistory", dr.IsDBNull(28) ? "" : dr.GetString(28));
            insuredInfo.Add("foreseeable", dr.IsDBNull(29) ? "" : dr.GetString(29));
            insuredInfo.Add("bloodPressureIllSystolic", dr.IsDBNull(30) ? "" : dr.GetString(30));
            insuredInfo.Add("bloodPressureIllDate", dr.IsDBNull(31) ? "" : dr.GetString(31));
            insuredInfo.Add("bloodPressureIllHospital", dr.IsDBNull(32) ? "" : dr.GetString(32));
            insuredInfo.Add("HyperlipidaemiaLevel", dr.IsDBNull(33) ? "" : dr.GetString(33));
            insuredInfo.Add("HyperlipidaemiaDate", dr.IsDBNull(34) ? "" : dr.GetString(34));
            insuredInfo.Add("HyperlipidaemiaHospital", dr.IsDBNull(35) ? "" : dr.GetString(35));
            insuredInfo.Add("PremiumLevel", dr.IsDBNull(36) ? -1 : dr.GetInt32(36));
            insuredInfo.Add("Premium", dr.IsDBNull(37) ? 0 : dr.GetDecimal(37));
            insuredInfo.Add("paidCount", dr.IsDBNull(38) ? 0 : dr.GetInt32(38));
            insuredInfo.Add("terminateReason", dr.IsDBNull(39) ? "" : dr.GetString(39));
            insuredInfo.Add("terminateDate", dr.IsDBNull(40) ? DateTime.MinValue : dr.GetDateTime(40));
            insuredInfo.Add("policyStatus", dr.IsDBNull(41) ? "" : dr.GetString(41));
            insuredInfo.Add("startDate", dr.IsDBNull(42) ? DateTime.MinValue : dr.GetDateTime(42));
            insuredInfo.Add("endDate", dr.IsDBNull(43) ? DateTime.MinValue : dr.GetDateTime(43));
            insuredInfo.Add("docPath", dr.IsDBNull(44) ? "" : dr.GetString(44));

        }
        dr.Close();


        return insuredInfo;
    }

    public Hashtable getInsuranceInfo(int insuredID)
    {
        string sql = "SELECT I.custID, I.premiumPeriod, I.category, D.insuredID, D.accountNo, D.fName, D.lName, D.NRIC, D.nationality, D.gender, D.marital, D.birth, D.planType, D.relationship, " +
                       "  D.occupation, D.telephone, D.longStay, D.mobile, D.email, D.address, D.postalCode, D.employerName, D.employerAddress, D.business, D.illness, D.illDuration,  " +
                       "  D.illTreatment, D.illhospital, D.DeclinedHistory, D.foreseeable, D.bloodPressureIllSystolic, D.bloodPressureIllDate, D.bloodPressureIllHospital,  " +
                       "  D.HyperlipidaemiaLevel, D.HyperlipidaemiaDate, D.HyperlipidaemiaHospital, D.PremiumLevel, D.Premium, D.paidCount, D.terminateReason, D.terminateDate, " +
                       "  D.policyStatus, D.startDate, D.endDate, D.docPath " +
                       "  FROM INSURED AS D INNER JOIN " +
                       "  INSURANCE AS I ON D.accountNo = I.accountNo  " +
                        "  WHERE  D.insuredID = @insuredID";
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
        SqlCommand cmd = new SqlCommand(sql, con);

        cmd.Parameters.AddWithValue("@insuredID", insuredID);
        SqlDataReader dr = cmd.ExecuteReader();
        var insuredInfo = new Hashtable();
        if (dr.Read())
        {
            insuredInfo.Add("custID", dr.IsDBNull(0) ? "" : dr.GetString(0));
            insuredInfo.Add("premiumPeriod", dr.IsDBNull(1) ? "" : dr.GetString(1));
            insuredInfo.Add("category", dr.IsDBNull(2) ? "" : dr.GetString(2));
            insuredInfo.Add("insuredID", dr.GetInt32(3));
            insuredInfo.Add("accountNo", dr.IsDBNull(4) ? "" : dr.GetString(4));
            insuredInfo.Add("fName", dr.IsDBNull(5) ? "" : dr.GetString(5));
            insuredInfo.Add("lName", dr.IsDBNull(6) ? "" : dr.GetString(6));
            insuredInfo.Add("NRIC", dr.IsDBNull(7) ? "" : dr.GetString(7));
            insuredInfo.Add("nationality", dr.IsDBNull(8) ? "" : dr.GetString(8));
            insuredInfo.Add("gender", dr.IsDBNull(9) ? "" : dr.GetString(9));
            insuredInfo.Add("marital", dr.IsDBNull(10) ? "" : dr.GetString(10));
            insuredInfo.Add("birth", dr.IsDBNull(11) ? DateTime.MinValue : dr.GetDateTime(11));
            insuredInfo.Add("planType", dr.IsDBNull(12) ? "" : dr.GetString(12));
            insuredInfo.Add("relationship", dr.IsDBNull(13) ? "" : dr.GetString(13));
            insuredInfo.Add("occupation", dr.IsDBNull(14) ? "" : dr.GetString(14));
            insuredInfo.Add("telephone", dr.IsDBNull(15) ? "" : dr.GetString(15));
            insuredInfo.Add("longStay", dr.IsDBNull(16) ? "" : dr.GetString(16));
            insuredInfo.Add("mobile", dr.IsDBNull(17) ? "" : dr.GetString(17));
            insuredInfo.Add("email", dr.IsDBNull(18) ? "" : dr.GetString(18));
            insuredInfo.Add("address", dr.IsDBNull(19) ? "" : dr.GetString(19));
            insuredInfo.Add("postalCode", dr.IsDBNull(20) ? "" : dr.GetString(20));
            insuredInfo.Add("employerName", dr.IsDBNull(21) ? "" : dr.GetString(21));
            insuredInfo.Add("employerAddress", dr.IsDBNull(22) ? "" : dr.GetString(22));
            insuredInfo.Add("business", dr.IsDBNull(23) ? "" : dr.GetString(23));
            insuredInfo.Add("illness", dr.IsDBNull(24) ? "" : dr.GetString(24));
            insuredInfo.Add("illDuration", dr.IsDBNull(25) ? "" : dr.GetString(25));
            insuredInfo.Add("illTreatment", dr.IsDBNull(26) ? "" : dr.GetString(26));
            insuredInfo.Add("illhospital", dr.IsDBNull(27) ? "" : dr.GetString(27));
            insuredInfo.Add("DeclinedHistory", dr.IsDBNull(28) ? "" : dr.GetString(28));
            insuredInfo.Add("foreseeable", dr.IsDBNull(29) ? "" : dr.GetString(29));
            insuredInfo.Add("bloodPressureIllSystolic", dr.IsDBNull(30) ? "" : dr.GetString(30));
            insuredInfo.Add("bloodPressureIllDate", dr.IsDBNull(31) ? "" : dr.GetString(31));
            insuredInfo.Add("bloodPressureIllHospital", dr.IsDBNull(32) ? "" : dr.GetString(32));
            insuredInfo.Add("HyperlipidaemiaLevel", dr.IsDBNull(33) ? "" : dr.GetString(33));
            insuredInfo.Add("HyperlipidaemiaDate", dr.IsDBNull(34) ? "" : dr.GetString(34));
            insuredInfo.Add("HyperlipidaemiaHospital", dr.IsDBNull(35) ? "" : dr.GetString(35));
            insuredInfo.Add("PremiumLevel", dr.IsDBNull(36) ? -1 : dr.GetInt32(36));
            insuredInfo.Add("Premium", dr.IsDBNull(37) ? 0 : dr.GetDecimal(37));
            insuredInfo.Add("paidCount", dr.IsDBNull(38) ? 0 : dr.GetInt32(38));
            insuredInfo.Add("terminateReason", dr.IsDBNull(39) ? "" : dr.GetString(39));
            insuredInfo.Add("terminateDate", dr.IsDBNull(40) ? DateTime.MinValue : dr.GetDateTime(40));
            insuredInfo.Add("policyStatus", dr.IsDBNull(41) ? "" : dr.GetString(41));
            insuredInfo.Add("startDate", dr.IsDBNull(42) ? DateTime.MinValue : dr.GetDateTime(42));
            insuredInfo.Add("endDate", dr.IsDBNull(43) ? DateTime.MinValue : dr.GetDateTime(43));
            insuredInfo.Add("docPath", dr.IsDBNull(44) ? "" : dr.GetString(44));

        }
        dr.Close();
        con.Close();

        return insuredInfo;
    }

    public Hashtable getInsuranceInfo(String accountNo, SqlConnection con, SqlTransaction myTrans)
    {
        string sql = " SELECT accountNo, balance, custID, premiumPeriod, premiumDiscount, category, product, productType, staffID, terminateReason, policyStatus, terminateDate FROM INSURANCE" +
                      " WHERE accountNo=@accountNo";


        SqlCommand cmd = new SqlCommand(sql, con, myTrans);
        cmd.Parameters.AddWithValue("@accountNo", accountNo);

        SqlDataReader dr = cmd.ExecuteReader();
        var insurance = new Hashtable();
        if (dr.Read())
        {
            insurance.Add("accountNo", dr.IsDBNull(0) ? "" : dr.GetString(0));
            insurance.Add("balance", dr.IsDBNull(1) ? 0 : dr.GetDecimal(1));
            insurance.Add("custID", dr.IsDBNull(2) ? "" : dr.GetString(2));
            insurance.Add("premiumPeriod", dr.IsDBNull(3) ? "" : dr.GetString(3));
            insurance.Add("premiumDiscount", dr.IsDBNull(4) ? 0 : dr.GetDecimal(4));
            insurance.Add("category", dr.IsDBNull(5) ? "" : dr.GetString(5));
            insurance.Add("product", dr.IsDBNull(6) ? "" : dr.GetString(6));
            insurance.Add("productType", dr.IsDBNull(7) ? "" : dr.GetString(7));
            insurance.Add("staffID", dr.IsDBNull(8) ? "" : dr.GetString(8));
            insurance.Add("terminateReason", dr.IsDBNull(9) ? "" : dr.GetString(9));
            insurance.Add("policyStatus", dr.IsDBNull(10) ? "" : dr.GetString(10));
            insurance.Add("terminateDate", dr.IsDBNull(11) ? DateTime.MinValue : dr.GetDateTime(11));
        }
        dr.Close();

        return insurance;
    }

    public Hashtable getInsuranceInfo(String accountNo)
    {
        string sql = " SELECT accountNo, balance, custID, premiumPeriod, premiumDiscount, category, product, productType, staffID, terminateReason, policyStatus, terminateDate FROM INSURANCE" +
                      " WHERE accountNo=@accountNo";
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@accountNo", accountNo);

        SqlDataReader dr = cmd.ExecuteReader();
        var insurance = new Hashtable();
        if (dr.Read())
        {
            insurance.Add("accountNo", dr.IsDBNull(0) ? "" : dr.GetString(0));
            insurance.Add("balance", dr.IsDBNull(1) ? 0 : dr.GetDecimal(1));
            insurance.Add("custID", dr.IsDBNull(2) ? "" : dr.GetString(2));
            insurance.Add("premiumPeriod", dr.IsDBNull(3) ? "" : dr.GetString(3));
            insurance.Add("premiumDiscount", dr.IsDBNull(4) ? 0 : dr.GetDecimal(4));
            insurance.Add("category", dr.IsDBNull(5) ? "" : dr.GetString(5));
            insurance.Add("product", dr.IsDBNull(6) ? "" : dr.GetString(6));
            insurance.Add("productType", dr.IsDBNull(7) ? "" : dr.GetString(7));
            insurance.Add("staffID", dr.IsDBNull(8) ? "" : dr.GetString(8));
            insurance.Add("terminateReason", dr.IsDBNull(9) ? "" : dr.GetString(9));
            insurance.Add("policyStatus", dr.IsDBNull(10) ? "" : dr.GetString(10));
            insurance.Add("terminateDate", dr.IsDBNull(11) ? DateTime.MinValue : dr.GetDateTime(11));
        }
        dr.Close();
        con.Close();

        return insurance;
    }

    public String getInsuranceSavingAccountNo(String custID)
    {
        string sql = "Select savingAccountNo from APPLICATION_INSURANCE Where custID = " + custID;
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();

        SqlCommand cmd = new SqlCommand(sql, con);
        SqlDataReader dr = cmd.ExecuteReader();
        String savingAccountNo = "";
        if (dr.Read())
        {
            savingAccountNo = dr.GetString(0);

        }
        dr.Close();
        con.Close();

        return savingAccountNo;

    }

    public Decimal getSavingBalance(String savingAccountNo)
    {
        string sql = "Select balance from SAVING_ACCOUNT Where accountNo = " + savingAccountNo;
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();

        SqlCommand cmd = new SqlCommand(sql, con);
        SqlDataReader dr = cmd.ExecuteReader();
        Decimal balance = 0;
        if (dr.Read())
        {
            balance = dr.GetDecimal(0);

        }
        dr.Close();
        con.Close();

        return balance;

    }

    //public Decimal getInsurancePremium(int applicationInsuredId, int level, String Category)
    //{
    //    CalculatePremiumBLL bll = new CalculatePremiumBLL();

    //    string sql = "select birth,planType,relationship from APPLICATION_INSURANCE_INSURED where insuredId=" + applicationInsuredId;
    //    SqlConnection con = DBManager.getSqlConnection();
    //    var cmd = new SqlCommand(sql, con);
    //    con.Open();
    //    SqlDataReader dr = cmd.ExecuteReader();
    //    DateTime birth = DateTime.MinValue;
    //    String planType = null;
    //    String relationship = null;

    //    if (dr.Read())
    //    {
    //        birth = dr.GetDateTime(0);
    //        planType = dr.GetString(1);
    //        relationship = dr.GetString(2);
    //    }
    //    dr.Close();
    //    con.Close();

    //    int age = int.Parse(DateTime.Now.ToString("yyyy")) - int.Parse(birth.ToString("yyyy"));
    //    Decimal premiun = bll.getPremium(relationship, age, planType, level, 0, Category);

    //    return premiun;
    //}

    public void transfer(String savingAccountNo, String toAccountNo, decimal amount, String transactionType)
    {
        SqlConnection con = null;
        SqlTransaction myTrans = null;
        try
        {
            con = DBManager.getSqlConnection();
            con.Open();
            // Start a local transaction
            myTrans = con.BeginTransaction();

            String sql = "insert into Summary (transactionDate,transactionType,transactionStatus,transactionAmount,accountNo) values " +
                "(@transactionDate,@transactionType,@transactionStatus,@transactionAmount,@accountNo)";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Transaction = myTrans;
            cmd.Parameters.AddWithValue("@transactionDate", DateTime.Now);
            cmd.Parameters.AddWithValue("@transactionType", transactionType);
            cmd.Parameters.AddWithValue("@transactionStatus", "SUCCESS");
            cmd.Parameters.AddWithValue("@transactionAmount", 0 - amount);
            cmd.Parameters.AddWithValue("@accountNo", savingAccountNo);
            cmd.ExecuteNonQuery();


            cmd = new SqlCommand(sql, con);
            cmd.Transaction = myTrans;
            cmd.Parameters.AddWithValue("@transactionDate", DateTime.Now);
            cmd.Parameters.AddWithValue("@transactionType", transactionType);
            cmd.Parameters.AddWithValue("@transactionStatus", "SUCCESS");
            cmd.Parameters.AddWithValue("@transactionAmount", amount);
            cmd.Parameters.AddWithValue("@accountNo", toAccountNo);
            cmd.ExecuteNonQuery();



            sql = "update SAVING_ACCOUNT " +
                " set balance =balance-@amount" +
                " where accountNo=@savingAccountNo";
            cmd = new SqlCommand(sql, con);
            cmd.Transaction = myTrans;
            cmd.Parameters.AddWithValue("@amount", amount);
            cmd.Parameters.AddWithValue("@savingAccountNo", savingAccountNo);
            cmd.ExecuteNonQuery();

            sql = "update INSURANCE" +
                " set balance =balance+@amount" +
                " where accountNo=@toAccountNo";
            cmd = new SqlCommand(sql, con);
            cmd.Transaction = myTrans;
            cmd.Parameters.AddWithValue("@amount", amount);
            cmd.Parameters.AddWithValue("@toAccountNo", toAccountNo);
            cmd.ExecuteNonQuery();

            AccountsManager manager = new AccountsManager();
            Hashtable insuranceInfo = manager.checkPremiumPaid(toAccountNo, con, myTrans);
            String premiumPeriod = insuranceInfo["premiumPeriod"] as String;
            sql = "update insured set paidCount=paidCount+@paidCount where insuredID=@insuredID";
            foreach (int key in insuranceInfo.Keys)
            {
                bool havePaid = (bool)insuranceInfo[key];
                if (!havePaid)
                {

                    cmd = new SqlCommand(sql, con, myTrans);

                    if ("Yearly".Equals(premiumPeriod))
                    {
                        cmd.Parameters.AddWithValue("@paidCount", 12);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@paidCount", 1);
                    }

                    cmd.Parameters.AddWithValue("@insuredID", key);
                    cmd.ExecuteNonQuery();
                }
            }






            myTrans.Commit();
        }
        catch (Exception e)
        {
            myTrans.Rollback();
            throw e;
        }
        finally
        {
            con.Close();

        }

    }

    public void transferFromInsuranceToSaving(String insuranceAccountNo, String savingAccountNo, decimal amount)
    {
        String STATUS = "INSURANCE";
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
        String sql = "insert into Summary (transactionDate,transactionType,transactionStatus,transactionAmount,accountNo) values (@transactionDate,@transactionType,@transactionStatus,@transactionAmount,@accountNo)";
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@transactionDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@transactionType", STATUS);
        cmd.Parameters.AddWithValue("@transactionStatus", "SUCCESS");
        cmd.Parameters.AddWithValue("@transactionAmount", 0 - amount);
        cmd.Parameters.AddWithValue("@accountNo", insuranceAccountNo);
        cmd.ExecuteNonQuery();


        cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@transactionDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@transactionType", STATUS);
        cmd.Parameters.AddWithValue("@transactionStatus", "SUCCESS");
        cmd.Parameters.AddWithValue("@transactionAmount", amount);
        cmd.Parameters.AddWithValue("@accountNo", savingAccountNo);

        cmd.ExecuteNonQuery();


        sql = " update SAVING_ACCOUNT " +
            " set balance =balance+@amount" +
            " where accountNo=@accontNo";
        cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@amount", amount);
        cmd.Parameters.AddWithValue("@accontNo", savingAccountNo);
        cmd.ExecuteNonQuery();


        sql = " update INSURANCE " +
           " set balance =balance-@amount" +
               " where accountNo=@accontNo";
        cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@amount", amount);
        cmd.Parameters.AddWithValue("@accontNo", insuranceAccountNo);
        cmd.ExecuteNonQuery();

        con.Close();

    }

    public String getTransactionType(String accountNo)
    {
        if (accountNo.StartsWith("520050"))
        {
            return "FUND TRANSFER";
        }
        if (accountNo.StartsWith("520060"))
        {
            return "FUND TRANSFER";
        }
        else if (accountNo.StartsWith("520070"))
        {
            return "INSURANCE PAYMENT";

        }
        else if (accountNo.StartsWith("520080"))
        {
            return "FIXED DEPOSIT PAYMENT";

        }
        else if (accountNo.StartsWith("520090"))
        {

            return "DEBIT PAYMENT";
        }
        else if (accountNo.StartsWith("FD5200"))
        {
            return "FIXED DEPOSIT CERTS";

        }
        else
        {
            return null;
        }


    }
    public DateTime getLastPaymentDate(String accountNo)
    {
        string sql = "Select Max(transactionDate) From SUMMARY Where accountNo = @accountNo";

        SqlConnection conn = DBManager.getSqlConnection();
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@accountNo", accountNo);
        conn.Open();

        SqlDataReader dr = cmd.ExecuteReader();
        DateTime lastDate = DateTime.MaxValue;

        if (dr.Read())
        {
            lastDate = dr.IsDBNull(0) ? DateTime.MaxValue : dr.GetDateTime(0);
        }
        dr.Close();

        conn.Close();

        return lastDate;
    }

    public int getCustIdFromAccount(int accountId)
    {
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
        String sql = "select userID from Account A " +
                      " where A.accountID=@acountID";
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@acountID", accountId);
        SqlDataReader dr = cmd.ExecuteReader();
        int userID = -1;
        while (dr.Read())
        {
           userID = dr.GetInt32(0);
        }
        dr.Close();
        con.Close();
        return userID;
    }




    public ArrayList getInsuredIds(String accountNo)
    {
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
        String sql = "select insuredID from INSURED " +
                      " where accountNo=@accontNo";
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@accontNo", accountNo);
        SqlDataReader dr = cmd.ExecuteReader();
        ArrayList list = new ArrayList();
        while (dr.Read())
        {
            list.Add(dr.GetInt32(0));
        }
        dr.Close();
        con.Close();
        return list;
    }


    public ArrayList getInsuredIds(String accountNo, SqlConnection con, SqlTransaction myTrans)
    {

        String sql = "select insuredID from INSURED " +
                      " where accountNo=@accontNo";
        SqlCommand cmd = new SqlCommand(sql, con, myTrans);
        cmd.Parameters.AddWithValue("@accontNo", accountNo);
        SqlDataReader dr = cmd.ExecuteReader();
        ArrayList list = new ArrayList();
        while (dr.Read())
        {
            list.Add(dr.GetInt32(0));
        }
        dr.Close();
        return list;
    }


    public Hashtable checkPremiumPaid(String accountNo)
    {
        Hashtable status = new Hashtable();
        AccountsManager manager = new AccountsManager();
        Hashtable info = manager.getInsuranceInfo(accountNo);

        ArrayList insuredIds = manager.getInsuredIds(accountNo);
        foreach (int insuredId in insuredIds)
        {
            Hashtable insuredIdInfo = manager.getInsuranceInfo(insuredId);
            String policyStatus = (String)insuredIdInfo["policyStatus"];

            if ("ACTIVE".Equals(policyStatus))
            {
                int paidCount = (int)insuredIdInfo["paidCount"];
                DateTime startDate = (DateTime)insuredIdInfo["startDate"];
                int yyyymmNow = int.Parse(DateTime.Now.ToString("yyyyMM"));
                int yyyymmStart = int.Parse(startDate.ToString("yyyyMM"));
                if (yyyymmNow - yyyymmStart >= paidCount)
                {
                    status.Add(insuredId, false);
                }
                else
                {
                    status.Add(insuredId, true);
                }
            }
            else
            {
                // if have terminated, consider as always Paid.
                status.Add(insuredId, true);
            }
        }

        return status;
    }

    public Hashtable checkPremiumPaid(String accountNo, SqlConnection con, SqlTransaction myTrans)
    {
        Hashtable status = new Hashtable();
        AccountsManager manager = new AccountsManager();
        Hashtable info = manager.getInsuranceInfo(accountNo, con, myTrans);

        ArrayList insuredIds = manager.getInsuredIds(accountNo, con, myTrans);
        foreach (int insuredId in insuredIds)
        {
            Hashtable insuredIdInfo = manager.getInsuranceInfo(insuredId, con, myTrans);
            String policyStatus = (String)insuredIdInfo["policyStatus"];

            if ("ACTIVE".Equals(policyStatus))
            {
                int paidCount = (int)insuredIdInfo["paidCount"];
                DateTime startDate = (DateTime)insuredIdInfo["startDate"];
                int yyyymmNow = int.Parse(DateTime.Now.ToString("yyyyMM"));
                int yyyymmStart = int.Parse(startDate.ToString("yyyyMM"));
                if (yyyymmNow - yyyymmStart >= paidCount)
                {
                    status.Add(insuredId, false);
                }
                else
                {
                    status.Add(insuredId, true);
                }
            }
            else
            {
                // if have terminated, consider as always Paid.
                status.Add(insuredId, true);
            }
        }

        return status;
    }


    public ArrayList checkOverDue(String accountNo)
    {
        AccountsManager manager = new AccountsManager();
        Hashtable info = manager.getInsuranceInfo(accountNo);

        ArrayList insuredIds = manager.getInsuredIds(accountNo);
        ArrayList insuredIdsOverDue = new ArrayList();
        foreach (int insuredId in insuredIds)
        {
            Hashtable insuredIdInfo = manager.getInsuranceInfo(insuredId);
            String policyStatus = (String)insuredIdInfo["policyStatus"];

            if ("ACTIVE".Equals(policyStatus))
            {
                int paidCount = (int)insuredIdInfo["paidCount"];
                DateTime startDate = (DateTime)insuredIdInfo["startDate"];
                int yyyymmNow = int.Parse(DateTime.Now.ToString("yyyyMM"));
                int yyyymmStart = int.Parse(startDate.ToString("yyyyMM"));
                if (yyyymmNow - yyyymmStart > paidCount)
                {
                    insuredIdsOverDue.Add(insuredId);
                }

            }

        }
        return insuredIdsOverDue;
    }


    public decimal getNeedPayAmount(String accountNo)
    {
        Hashtable paidInsured = checkPremiumPaid(accountNo);
        decimal totalAmount = 0;
        foreach (int key in paidInsured.Keys)
        {
            bool havePaid = (bool)paidInsured[key];
            if (!havePaid)
            {
                Hashtable insuranceInfo = getInsuranceInfo(key);
                decimal amount = (decimal)insuranceInfo["Premium"];
                totalAmount += amount;
            }
        }
        return totalAmount;
    }


}